﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using Maticsoft.DBUtility;
namespace BLL
{
    public class UserOrganizationBLL
    {
        public static DataSet FindOrganizationDetailByID(int id)
        {
            string sqlstr = "select * from user_Organization where usermemberid = " + id;
            return DbHelperOra.Query(sqlstr);
        }

        public static int AddUpdateUserOrganization(int id, string dblNo, string nickName,
            string fullName, string keyWord1, string keyWord2, string keyWord3,
                string address, string description, string phone, string fax,
                    string email, string mobilePhone, string qq, string msn,
                        string blog, string weibo, string website)//infostate默认为0,infotime默认为当前时间
        {
            string sqlselect = "select * from user_Organization where usermemberid = " + id;
            DataSet dsSelect = DbHelperOra.Query(sqlselect);
            if (dsSelect.Tables[0].Rows.Count == 0)
            {
                //add
                return AddUserOrganization(id,dblNo, nickName, fullName, keyWord1, keyWord2, keyWord3,
                    address, description, phone, fax, email, mobilePhone, qq, msn, blog, weibo, website);
            }
            else
            {
                //update

                return UpdateUserOrganization(id,  nickName, fullName, keyWord1, keyWord2, keyWord3,
                    address, description, phone, fax, email, mobilePhone, qq, msn, blog, weibo, website);
            }
        }

        private static int AddUserOrganization(int id,string dblNo, string nickName,
            string fullName, string keyWord1, string keyWord2, string keyWord3,
                string address, string description, string phone, string fax,
                    string email, string mobilePhone, string qq, string msn,
                        string blog, string weibo, string website)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"insert into user_Organization (usermemberid,dblno,nickname,fullname,keyword1,
                        keyword2,keyword3,address,description,phone,fax,email,mobilephone,qq,
                        msn,blog,weibo,website,infostate,infotime) ");
            sb.Append(@"values(:v_usermemberid,:v_dblno,:v_nickname,:v_fullname,:v_keyword1,
                        :v_keyword2,:v_keyword3,:v_address,:v_description,:v_phone,:v_fax,
                        :v_email,:v_mobilephone,:v_qq,:v_msn,
                        :v_blog,:v_weibo,:v_website,:v_infostate,:v_infotime) ");

            OracleParameter[] para = {
                new OracleParameter(":v_dblno",OracleType.VarChar),
                new OracleParameter(":v_nickname",OracleType.VarChar),
                new OracleParameter(":v_fullname",OracleType.VarChar),
                new OracleParameter(":v_keyword1",OracleType.VarChar),
                new OracleParameter(":v_keyword2",OracleType.VarChar),
                new OracleParameter(":v_keyword3",OracleType.VarChar),
                new OracleParameter(":v_address",OracleType.VarChar),
                new OracleParameter(":v_description", OracleType.VarChar),
                new OracleParameter(":v_phone",OracleType.VarChar),
                new OracleParameter(":v_fax",OracleType.VarChar),
                new OracleParameter(":v_email",OracleType.VarChar),
                new OracleParameter(":v_mobilephone",OracleType.VarChar),
                new OracleParameter(":v_qq",OracleType.VarChar),
                new OracleParameter(":v_msn",OracleType.VarChar),
                new OracleParameter(":v_blog",OracleType.VarChar),
                new OracleParameter(":v_weibo",OracleType.VarChar),
                new OracleParameter(":v_website",OracleType.VarChar),
                new OracleParameter(":v_infostate",OracleType.VarChar),
                new OracleParameter(":v_infotime",OracleType.DateTime),
                new OracleParameter(":v_usermemberid",OracleType.Number)
            };
            para[0].Value = dblNo;
            para[1].Value = nickName;
            para[2].Value = fullName;
            para[3].Value = keyWord1;
            para[4].Value = keyWord2;
            para[5].Value = keyWord3;
            para[6].Value = address;
            para[7].Value = description;
            para[8].Value = phone;
            para[9].Value = fax;
            para[10].Value = email;
            para[11].Value = mobilePhone;
            para[12].Value = qq;
            para[13].Value = msn;
            para[14].Value = blog;
            para[15].Value = weibo;
            para[16].Value = website;
            para[17].Value = "0";
            para[18].Value = System.DateTime.Now;
            para[19].Value = id;
            return DbHelperOra.ExecuteSql(sb.ToString(), para);
        }


        private static int UpdateUserOrganization(int usermemberid,  string nickName,
            string fullName, string keyWord1, string keyWord2, string keyWord3,
                string address, string description, string phone, string fax,
                    string email, string mobilePhone, string qq, string msn,
                        string blog, string weibo, string website)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"update user_Organization
                           set 
                               
                               nickname = :v_nickname,
                               fullname = :v_fullname,
                               keyword1 = :v_keyword1,
                               keyword2 = :v_keyword2,
                               keyword3 = :v_keyword3,
                               address = :v_address,
                               description = :v_description,
                               phone = :v_phone,
                               fax = :v_fax,
                               email = :v_email,
                               mobilephone = :v_mobilephone,
                               infostate = :v_infostate,
                               infotime = :v_infotime,
                               qq = :v_qq,
                               msn = :v_msn,
                               blog = :v_blog,
                               weibo = :v_weibo,
                               website = :v_website
                         where usermemberid = :v_usermemberid");

            OracleParameter[] para = {
                
                new OracleParameter(":v_nickname",OracleType.VarChar),
                new OracleParameter(":v_fullname",OracleType.VarChar),
                new OracleParameter(":v_keyword1",OracleType.VarChar),
                new OracleParameter(":v_keyword2",OracleType.VarChar),
                new OracleParameter(":v_keyword3",OracleType.VarChar),
                new OracleParameter(":v_address",OracleType.VarChar),
                new OracleParameter(":v_description", OracleType.VarChar),
                new OracleParameter(":v_phone",OracleType.VarChar),
                new OracleParameter(":v_fax",OracleType.VarChar),
                new OracleParameter(":v_email",OracleType.VarChar),
                new OracleParameter(":v_mobilephone",OracleType.VarChar),
                new OracleParameter(":v_qq",OracleType.VarChar),
                new OracleParameter(":v_msn",OracleType.VarChar),
                new OracleParameter(":v_blog",OracleType.VarChar),
                new OracleParameter(":v_weibo",OracleType.VarChar),
                new OracleParameter(":v_website",OracleType.VarChar),
                new OracleParameter(":v_infostate",OracleType.VarChar),
                new OracleParameter(":v_infotime",OracleType.DateTime),
                new OracleParameter(":v_usermemberid",OracleType.Number)
            };
           
            para[0].Value = nickName;
            para[1].Value = fullName;
            para[2].Value = keyWord1;
            para[3].Value = keyWord2;
            para[4].Value = keyWord3;
            para[5].Value = address;
            para[6].Value = description;
            para[7].Value = phone;
            para[8].Value = fax;
            para[9].Value = email;
            para[10].Value = mobilePhone;
            para[11].Value = qq;
            para[12].Value = msn;
            para[13].Value = blog;
            para[14].Value = weibo;
            para[15].Value = website;
            para[16].Value = "0";
            para[17].Value = System.DateTime.Now;
            para[18].Value = usermemberid;

            return DbHelperOra.ExecuteSql(sb.ToString(), para);
        }
        public static int ApproveUserOrganization(int usermemberid)
        {
            string sqlstr = @"update user_Organization 
                                set infostate = :v_infostate,
                               infotime = :v_infotime where usermemberid = :v_usermemberid";
            OracleParameter[] para = {new OracleParameter(":v_infostate",OracleType.VarChar),
                                      new OracleParameter(":v_infotime",OracleType.DateTime),
                                     new OracleParameter(":v_usermemberid",OracleType.Number)};
            para[0].Value = "1";//已审核通过
            para[1].Value = System.DateTime.Now;
            para[2].Value = usermemberid;
            return DbHelperOra.ExecuteSql(sqlstr, para);
        }
        public static int NotApproveUserOrganization(int usermemberid)
        {
            string sqlstr = @"update user_Organization 
                                set infostate = :v_infostate,
                               infotime = :v_infotime where usermemberid = :v_usermemberid";
            OracleParameter[] para = {new OracleParameter(":v_infostate",OracleType.VarChar),
                                      new OracleParameter(":v_infotime",OracleType.DateTime),
                                     new OracleParameter(":v_usermemberid",OracleType.Number)};
            para[0].Value = "0";//已审核不通过
            para[1].Value = System.DateTime.Now;
            para[2].Value = usermemberid;
            return DbHelperOra.ExecuteSql(sqlstr, para);
        }
    }
}
